﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlServerCe;
using System.Data.SqlTypes;
using GoTextPalm.Core.Data;

namespace GoTextPalm.Core.Persistence
{
    public class DBMessage
    {
        public static void DeleteMessage(int IDMessage)
        {
            try
            {
                SqlCeCommand cmd = DB.Instance.NewCommand();
                cmd.CommandText = "DELETE FROM RecipientsForMessagesSent WHERE idMessage = @id";
                cmd.Parameters.Add("@id", IDMessage);
                cmd.ExecuteNonQuery();
                cmd.CommandText = "DELETE FROM MessagesSent WHERE idMessage = @id";
                cmd.Parameters.Add("@id", IDMessage);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            catch (SqlCeException e)
            {
                throw new DBException(e);
            }
        }

        public static void SetLastMessageSent(int IDService, ref Message mex)
        {
            try
            {
                SqlCeCommand cmd = DB.Instance.NewCommand();
                cmd.CommandText = "SELECT idMessage,saved FROM LastMessageSent WHERE idService = @ids";
                cmd.Parameters.Add("@ids", IDService);
                SqlCeDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    SqlInt32 idm = dr.GetSqlInt32(0);
                    bool saved = dr.GetBoolean(1);
                    if (!saved && !idm.IsNull)
                    {
                        SqlCeCommand cmd2 = DB.Instance.NewCommand();
                        cmd2.CommandText = "DELETE FROM MessagesSent WHERE idMessage = @idm";
                        cmd2.Parameters.Add("@idm", idm);
                        cmd2.ExecuteNonQuery();
                        cmd2.Dispose();
                    }
                }
                dr.Dispose();
                cmd.CommandText = "UPDATE LastMessageSent SET idMessage = @idm, saved = @saved WHERE idService = @ids";
                bool rsaved = true;
                if (mex.ID.IsNull)
                {
                    AddMessage(IDService, ref mex);
                    rsaved = false;
                }
                cmd.Parameters.Add("@idm", mex.ID);
                cmd.Parameters.Add("@saved", rsaved);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            catch (Exception e)
            {
                throw new DBException((SqlCeException)e);
            }
        }

        public static void AddMessage(int IDService, ref Message mex)
        {
            try
            {
                SqlCeCommand cmd = DB.Instance.NewCommand();
                cmd.CommandText = "INSERT INTO MessagesSent (idService, sentDate, " +
                    "message) VALUES (@id, @date, @mex)";
                cmd.Parameters.Add("@id", IDService);
                cmd.Parameters.Add("@date", mex.Now = DateTime.Now);
                cmd.Parameters.Add("@mex", mex.Text);
                cmd.ExecuteNonQuery();
                cmd.CommandText = "SELECT @@IDENTITY FROM MessagesSent";
                cmd.Parameters.Clear();
                mex.ID = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.CommandText = "INSERT INTO RecipientsForMessagesSent (idMessageSent, " +
                    "label, value) VALUES (@id, @label, @value)";
                SqlCeCommand cmd2 = DB.Instance.NewCommand();
                cmd2.CommandText = "SELECT @@IDENTITY FROM RecipientsForMessagesSent";
                foreach (MessageRecipient mr in mex.Recipients)
                {
                    cmd.Parameters.Add("@id", mex.ID);
                    cmd.Parameters.Add("@label", mr.Label);
                    cmd.Parameters.Add("@value", mr.Value);
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    mr.ID = Convert.ToInt32(cmd2.ExecuteScalar());
                }
                cmd.Dispose();
                cmd2.Dispose();
            }
            catch (SqlCeException e)
            {
                throw new DBException(e);
            }
        }

        public static List<Message> GetMessages(int IDService)
        {
            try
            {
                SqlCeCommand cmd = DB.Instance.NewCommand();
                SqlCeCommand cmd2 = DB.Instance.NewCommand();
                cmd.CommandText = "SELECT idMessage,sentDate,message FROM MessagesSent WHERE idService = @id";
                cmd.Parameters.Add("@id", IDService);
                cmd2.CommandText = "SELECT idRecipient,label,value FROM RecipientsForMessagesSent WHERE idMessageSent = @id";
                SqlCeDataReader dr = cmd.ExecuteReader();
                List<Message> ml = new List<Message>();
                while (dr.Read())
                {
                    Message m = new Message
                    {
                        ID = dr.GetInt32(0),
                        Now = dr.GetDateTime(1),
                        Text = dr.GetString(2)
                    };
                    cmd2.Parameters.Add("@id", m.ID);
                    SqlCeDataReader dr2 = cmd2.ExecuteReader();
                    while (dr2.Read())
                        m.Recipients.Add(new MessageRecipient
                        {
                            ID = dr2.GetInt32(0),
                            Label = dr2.GetString(1),
                            Value = dr2.GetString(2)
                        });
                    dr2.Dispose();
                    cmd2.Parameters.Clear();
                    ml.Add(m);
                }
                dr.Dispose();
                cmd2.Dispose();
                cmd.Dispose();
                return ml;
            }
            catch (SqlCeException e)
            {
                throw new DBException(e);
            }
        }
    }
}
